-- declare @KSSJ VARCHAR(20),@JSSJ VARCHAR(20)
-- SET @KSSJ='2025-10-01 00:00:00'
-- SET @JSSJ='2025-10-31 23:59:59'


with
    TIME AS (SELECT '2025-10-01 00:00:00' AS KSSJ,'2025-10-31 23:59:59' AS JSSJ),
    outp_settle_info_bt as (
    SELECT
       '1' as LB,
        EXT3.SETL_INSUR_REIMBUR_ITEM_MEMO 清算机构代码,
        V3.VALUE_DESC 清算机构,
        EXT1.SETL_INSUR_REIMBUR_ITEM_MEMO 险种类型代码,
        V2.VALUE_DESC 险种,
        EXT2.SETL_INSUR_REIMBUR_ITEM_MEMO 清算类别代码,
        (case EXT2.SETL_INSUR_REIMBUR_ITEM_MEMO
            when '9907'then '总控'
            when '9908'then '非总控'
            when '9909'then '域内异地'
            when '99952'then '异地医疗费用清算'
            when '99999'then '返外地人员清算'
            when '99974'then 'DRG支付'
            else '其他' end)清算类别,
        D.MED_CATEGORY_CODE 医疗类别代码,
        V1.VALUE_DESC  医疗类别,
				COUNT(DISTINCT S.SETTLEMENT_ID) 笔数,
       SUM(CASE WHEN REIM.MED_INSUR_REIMBUR_ITEM_CODE=399559972 then REIM.SETL_INSUR_REIMBUR_ITEM_AMOUNT ELSE 0 END) 医疗费总金额,
        SUM(CASE WHEN REIM.MED_INSUR_REIMBUR_ITEM_CODE=399559986 then REIM.SETL_INSUR_REIMBUR_ITEM_AMOUNT ELSE 0 END) 基金总金额,
        SUM(CASE WHEN REIM.MED_INSUR_REIMBUR_ITEM_CODE=399559978 then REIM.SETL_INSUR_REIMBUR_ITEM_AMOUNT ELSE 0 END) 统筹基金支付,
        SUM(CASE WHEN REIM.MED_INSUR_REIMBUR_ITEM_CODE=399559988 then REIM.SETL_INSUR_REIMBUR_ITEM_AMOUNT ELSE 0 END) 账户支付,
        SUM(CASE WHEN REIM.MED_INSUR_REIMBUR_ITEM_CODE=399559980 then REIM.SETL_INSUR_REIMBUR_ITEM_AMOUNT ELSE 0 END) 公务员补助,
        SUM(CASE WHEN REIM.MED_INSUR_REIMBUR_ITEM_CODE=399559981 then REIM.SETL_INSUR_REIMBUR_ITEM_AMOUNT ELSE 0 END) 企业补助,
        SUM(CASE WHEN REIM.MED_INSUR_REIMBUR_ITEM_CODE=399559982 then REIM.SETL_INSUR_REIMBUR_ITEM_AMOUNT ELSE 0 END) 居民大病,
        SUM(CASE WHEN REIM.MED_INSUR_REIMBUR_ITEM_CODE=399559983 then REIM.SETL_INSUR_REIMBUR_ITEM_AMOUNT ELSE 0 END) 职工大额,
        SUM(CASE WHEN REIM.MED_INSUR_REIMBUR_ITEM_CODE=399559984 then REIM.SETL_INSUR_REIMBUR_ITEM_AMOUNT ELSE 0 END) 医疗救助,
        SUM(CASE WHEN REIM.MED_INSUR_REIMBUR_ITEM_CODE=399559985 then REIM.SETL_INSUR_REIMBUR_ITEM_AMOUNT ELSE 0 END) 其他支出,
        SUM(CASE WHEN REIM.MED_INSUR_REIMBUR_ITEM_CODE=399559989 then REIM.SETL_INSUR_REIMBUR_ITEM_AMOUNT ELSE 0 END) 个人现金,
        SUM(CASE WHEN REIM.MED_INSUR_REIMBUR_ITEM_CODE=399559990 then REIM.SETL_INSUR_REIMBUR_ITEM_AMOUNT ELSE 0 END) 医院负担金额
        FROM WINDBA.SETTLEMENT S
     JOIN WINDBA.SETTLEMENT_INSUR_REIM_DETAIL D ON S.SETTLEMENT_ID=D.SETTLEMENT_ID AND D.IS_DEL=0 AND D.THIRD_PARTY_TXN_SEQ_NO IS NOT NULL --医保
     LEFT JOIN WINDBA.VALUE_SET V1 ON D.MED_CATEGORY_CODE = V1.VALUE_ID --医疗类别
     LEFT JOIN WINDBA.SETTLEMENT_INSUR_REIM_ITEM_EXT EXT1 ON  D.SETL_INSUR_REIM_DETAIL_ID=EXT1.SETL_INSUR_REIM_DETAIL_ID
                        AND EXT1.MED_INSUR_REIMBUR_ITEM_CODE=399559966 AND EXT1.IS_DEL=0 --险种
     LEFT JOIN WINDBA.VALUE_SET V2 ON EXT1.SETL_INSUR_REIMBUR_ITEM_MEMO=V2.VALUE_NO AND V2.CODE_SYSTEM_ID=399628923
     LEFT JOIN WINDBA.SETTLEMENT_INSUR_REIM_ITEM_EXT EXT2 ON  D.SETL_INSUR_REIM_DETAIL_ID=EXT2.SETL_INSUR_REIM_DETAIL_ID
                        AND EXT2.MED_INSUR_REIMBUR_ITEM_CODE=399559996  AND EXT2.IS_DEL=0 --清算类别
     LEFT JOIN WINDBA.SETTLEMENT_INSUR_REIM_ITEM_EXT EXT3 ON  D.SETL_INSUR_REIM_DETAIL_ID=EXT3.SETL_INSUR_REIM_DETAIL_ID
                        AND EXT3.MED_INSUR_REIMBUR_ITEM_CODE=399559994  AND EXT3.IS_DEL=0 --清算经办机构
     LEFT JOIN WINDBA.SETTLEMENT_INSUR_REIM_ITEM REIM ON D.SETL_INSUR_REIM_DETAIL_ID = REIM.SETL_INSUR_REIM_DETAIL_ID
                        AND REIM.IS_DEL=0
     LEFT JOIN WINDBA.VALUE_SET V3 ON EXT3.SETL_INSUR_REIMBUR_ITEM_MEMO=V3.VALUE_NO AND V3.CODE_SYSTEM_ID=97819 -- 清算机构
    WHERE S.IS_DEL=0 AND S.SETTLEMENT_STATUS = 952093 --完成
                -- AND S.SETTLEMENT_ACCOUNT_STATUS = 307301 -- 正常
          AND S.VALID_FLAG = 98175 and D.MED_INSTI_INTERFACE_ID =1143183990430105600 -- 兵团医保
 AND  S.SETTLED_AT BETWEEN (SELECT KSSJ FROM TIME) AND  (SELECT JSSJ FROM TIME) --根据结算时间过滤
-- 		 ETC(AND CHARINDEX(CAST(CHARGING_HOSPITAL_ID AS NVARCHAR(24)), #{CHARGING_HOSPITAL_ID})> 0)

        GROUP BY D.MED_CATEGORY_CODE,EXT2.SETL_INSUR_REIMBUR_ITEM_MEMO,EXT3.SETL_INSUR_REIMBUR_ITEM_MEMO,EXT1.SETL_INSUR_REIMBUR_ITEM_MEMO,
        V1.VALUE_DESC,V2.VALUE_DESC,V3.VALUE_DESC
),
inp_settle_info_bt as (
    SELECT
   '2' as LB,
        EXT3.SETL_INSUR_REIMBUR_ITEM_MEMO 清算机构代码,
        V3.VALUE_DESC 清算机构,
        EXT1.SETL_INSUR_REIMBUR_ITEM_MEMO 险种类型代码,
        V2.VALUE_DESC 险种,
        EXT2.SETL_INSUR_REIMBUR_ITEM_MEMO 清算类别代码,
        (case EXT2.SETL_INSUR_REIMBUR_ITEM_MEMO
            when '9907'then '总控'
            when '9908'then '非总控'
            when '9909'then '域内异地'
            when '99952'then '异地医疗费用清算'
            when '99999'then '返外地人员清算'
            when '99974'then 'DRG支付'
            else '其他' end)清算类别,
        II.MEDICAL_CATEGORY_CODE 医疗类别代码,
        V1.VALUE_DESC  医疗类别,
		COUNT(DISTINCT S.SETTLEMENT_ID) 笔数,
        SUM(CASE WHEN REIM.MED_INSUR_REIMBUR_ITEM_CODE=399559972 then REIM.SETL_INSUR_REIMBUR_ITEM_AMOUNT ELSE 0 END) 医疗费总金额,
        SUM(CASE WHEN REIM.MED_INSUR_REIMBUR_ITEM_CODE=399559986 then REIM.SETL_INSUR_REIMBUR_ITEM_AMOUNT ELSE 0 END) 基金总金额,
        SUM(CASE WHEN REIM.MED_INSUR_REIMBUR_ITEM_CODE=399559978 then REIM.SETL_INSUR_REIMBUR_ITEM_AMOUNT ELSE 0 END) 统筹基金支付,
        SUM(CASE WHEN REIM.MED_INSUR_REIMBUR_ITEM_CODE=399559988 then REIM.SETL_INSUR_REIMBUR_ITEM_AMOUNT ELSE 0 END) 账户支付,
        SUM(CASE WHEN REIM.MED_INSUR_REIMBUR_ITEM_CODE=399559980 then REIM.SETL_INSUR_REIMBUR_ITEM_AMOUNT ELSE 0 END) 公务员补助,
        SUM(CASE WHEN REIM.MED_INSUR_REIMBUR_ITEM_CODE=399559981 then REIM.SETL_INSUR_REIMBUR_ITEM_AMOUNT ELSE 0 END) 企业补助,
        SUM(CASE WHEN REIM.MED_INSUR_REIMBUR_ITEM_CODE=399559982 then REIM.SETL_INSUR_REIMBUR_ITEM_AMOUNT ELSE 0 END) 居民大病,
        SUM(CASE WHEN REIM.MED_INSUR_REIMBUR_ITEM_CODE=399559983 then REIM.SETL_INSUR_REIMBUR_ITEM_AMOUNT ELSE 0 END) 职工大额,
        SUM(CASE WHEN REIM.MED_INSUR_REIMBUR_ITEM_CODE=399559984 then REIM.SETL_INSUR_REIMBUR_ITEM_AMOUNT ELSE 0 END) 医疗救助,
        SUM(CASE WHEN REIM.MED_INSUR_REIMBUR_ITEM_CODE=399559985 then REIM.SETL_INSUR_REIMBUR_ITEM_AMOUNT ELSE 0 END) 其他支出,
        SUM(CASE WHEN REIM.MED_INSUR_REIMBUR_ITEM_CODE=399559989 then REIM.SETL_INSUR_REIMBUR_ITEM_AMOUNT ELSE 0 END) 个人现金,
        SUM(CASE WHEN REIM.MED_INSUR_REIMBUR_ITEM_CODE=399559990 then REIM.SETL_INSUR_REIMBUR_ITEM_AMOUNT ELSE 0 END) 医院负担金额
        FROM WINDBA.INPATIENT_SETTLEMENT S
     JOIN WINDBA.INP_SETT_INSUR_REIM_DETAIL D ON S.SETTLEMENT_ID=D.SETTLEMENT_ID AND D.IS_DEL=0 AND D.THIRD_PARTY_TXN_SEQ_NO IS NOT NULL --医保
     JOIN WINDBA.INPATIENT_ENCOUNTER IE ON S.ENCOUNTER_ID = IE.ENCOUNTER_ID AND IE.IS_DEL = 0
     LEFT JOIN WINDBA.INPATIENT_INSURANCE II ON S.ENCOUNTER_ID = II.ENCOUNTER_ID AND II.IS_DEL = 0
     LEFT JOIN WINDBA.VALUE_SET V1 ON II.MEDICAL_CATEGORY_CODE = V1.VALUE_ID --医疗类别
     LEFT JOIN WINDBA.INP_SETT_INSUR_REIM_ITEM EXT1 ON  D.SETL_INSUR_REIM_DETAIL_ID=EXT1.SETL_INSUR_REIM_DETAIL_ID
                        AND EXT1.MED_INSUR_REIMBUR_ITEM_CODE=399559966 AND EXT1.IS_DEL=0 --险种
     LEFT JOIN WINDBA.VALUE_SET V2 ON EXT1.SETL_INSUR_REIMBUR_ITEM_MEMO=V2.VALUE_NO AND V2.CODE_SYSTEM_ID=399628923
     LEFT JOIN WINDBA.INP_SETT_INSUR_REIM_ITEM EXT2 ON  D.SETL_INSUR_REIM_DETAIL_ID=EXT2.SETL_INSUR_REIM_DETAIL_ID
                        AND EXT2.MED_INSUR_REIMBUR_ITEM_CODE=399559996  AND EXT2.IS_DEL=0 --清算类别
     LEFT JOIN WINDBA.INP_SETT_INSUR_REIM_ITEM EXT3 ON  D.SETL_INSUR_REIM_DETAIL_ID=EXT3.SETL_INSUR_REIM_DETAIL_ID
                        AND EXT3.MED_INSUR_REIMBUR_ITEM_CODE=399559994  AND EXT3.IS_DEL=0 --清算经办机构
     LEFT JOIN WINDBA.INP_SETT_INSUR_REIM_ITEM REIM ON D.SETL_INSUR_REIM_DETAIL_ID = REIM.SETL_INSUR_REIM_DETAIL_ID
                        AND REIM.IS_DEL=0
     LEFT JOIN WINDBA.VALUE_SET V3 ON EXT3.SETL_INSUR_REIMBUR_ITEM_MEMO=V3.VALUE_NO AND V3.CODE_SYSTEM_ID=97819 -- 清算机构
    WHERE S.IS_DEL=0 AND S.SETTLEMENT_STATUS = 952093 --完成
                -- AND S.SETTLEMENT_ACCOUNT_STATUS = 307301 -- 正常
          AND S.VALID_FLAG = 98175 and D.MED_INSTI_INTERFACE_ID =1143183990430105600 -- 兵团医保
 AND  S.SETTLED_AT BETWEEN  (SELECT KSSJ FROM TIME) AND  (SELECT JSSJ FROM TIME)  --根据结算时间过滤
-- 		 ETC(AND CHARINDEX(CAST(CHARGING_HOSPITAL_ID AS NVARCHAR(24)), #{CHARGING_HOSPITAL_ID})> 0)
        GROUP BY II.MEDICAL_CATEGORY_CODE,EXT2.SETL_INSUR_REIMBUR_ITEM_MEMO,EXT3.SETL_INSUR_REIMBUR_ITEM_MEMO,EXT1.SETL_INSUR_REIMBUR_ITEM_MEMO,
        V1.VALUE_DESC,V2.VALUE_DESC,V3.VALUE_DESC
),outp_settle_info_dq as (
    SELECT
   '1' as LB,
        EXT3.SETL_INSUR_REIMBUR_ITEM_MEMO 清算机构代码,
        V3.VALUE_DESC 清算机构,
        EXT1.SETL_INSUR_REIMBUR_ITEM_MEMO 险种类型代码,
        V2.VALUE_DESC 险种,
        EXT2.SETL_INSUR_REIMBUR_ITEM_MEMO 清算类别代码,
        EXT2.SETL_INSUR_REIMBUR_ITEM_MEMO 清算类别,
        D.MED_CATEGORY_CODE 医疗类别代码,
        V1.VALUE_DESC  医疗类别,
        COUNT(DISTINCT S.SETTLEMENT_ID) 笔数,
        SUM(CASE WHEN REIM.MED_INSUR_REIMBUR_ITEM_CODE=399559972 then REIM.SETL_INSUR_REIMBUR_ITEM_AMOUNT ELSE 0 END) 医疗费总金额,
        SUM(CASE WHEN REIM.MED_INSUR_REIMBUR_ITEM_CODE=399559986 then REIM.SETL_INSUR_REIMBUR_ITEM_AMOUNT ELSE 0 END) 基金总金额,
        SUM(CASE WHEN REIM.MED_INSUR_REIMBUR_ITEM_CODE=399559978 then REIM.SETL_INSUR_REIMBUR_ITEM_AMOUNT ELSE 0 END) 统筹基金支付,
        SUM(CASE WHEN REIM.MED_INSUR_REIMBUR_ITEM_CODE=399559988 then REIM.SETL_INSUR_REIMBUR_ITEM_AMOUNT ELSE 0 END) 账户支付,
        SUM(CASE WHEN REIM.MED_INSUR_REIMBUR_ITEM_CODE=399559980 then REIM.SETL_INSUR_REIMBUR_ITEM_AMOUNT ELSE 0 END) 公务员补助,
        SUM(CASE WHEN REIM.MED_INSUR_REIMBUR_ITEM_CODE=399559981 then REIM.SETL_INSUR_REIMBUR_ITEM_AMOUNT ELSE 0 END) 企业补助,
        SUM(CASE WHEN REIM.MED_INSUR_REIMBUR_ITEM_CODE=399559982 then REIM.SETL_INSUR_REIMBUR_ITEM_AMOUNT ELSE 0 END) 居民大病,
        SUM(CASE WHEN REIM.MED_INSUR_REIMBUR_ITEM_CODE=399559983 then REIM.SETL_INSUR_REIMBUR_ITEM_AMOUNT ELSE 0 END) 职工大额,
        SUM(CASE WHEN REIM.MED_INSUR_REIMBUR_ITEM_CODE=399559984 then REIM.SETL_INSUR_REIMBUR_ITEM_AMOUNT ELSE 0 END) 医疗救助,
        SUM(CASE WHEN REIM.MED_INSUR_REIMBUR_ITEM_CODE=399559985 then REIM.SETL_INSUR_REIMBUR_ITEM_AMOUNT ELSE 0 END) 其他支出,
        SUM(CASE WHEN REIM.MED_INSUR_REIMBUR_ITEM_CODE=399559989 then REIM.SETL_INSUR_REIMBUR_ITEM_AMOUNT ELSE 0 END) 个人现金,
        SUM(CASE WHEN REIM.MED_INSUR_REIMBUR_ITEM_CODE=399559990 then REIM.SETL_INSUR_REIMBUR_ITEM_AMOUNT ELSE 0 END) 医院负担金额
        FROM WINDBA.SETTLEMENT S
     JOIN WINDBA.SETTLEMENT_INSUR_REIM_DETAIL D ON S.SETTLEMENT_ID=D.SETTLEMENT_ID AND D.IS_DEL=0 AND D.THIRD_PARTY_TXN_SEQ_NO IS NOT NULL --医保
     LEFT JOIN WINDBA.VALUE_SET V1 ON D.MED_CATEGORY_CODE = V1.VALUE_ID --医疗类别
     LEFT JOIN WINDBA.SETTLEMENT_INSUR_REIM_ITEM_EXT EXT1 ON  D.SETL_INSUR_REIM_DETAIL_ID=EXT1.SETL_INSUR_REIM_DETAIL_ID
                        AND EXT1.MED_INSUR_REIMBUR_ITEM_CODE=399559966 AND EXT1.IS_DEL=0 --险种
     LEFT JOIN WINDBA.VALUE_SET V2 ON EXT1.SETL_INSUR_REIMBUR_ITEM_MEMO=V2.VALUE_NO AND V2.CODE_SYSTEM_ID=399628923
     LEFT JOIN WINDBA.SETTLEMENT_INSUR_REIM_ITEM_EXT EXT2 ON  D.SETL_INSUR_REIM_DETAIL_ID=EXT2.SETL_INSUR_REIM_DETAIL_ID
                        AND EXT2.MED_INSUR_REIMBUR_ITEM_CODE=399559996  AND EXT2.IS_DEL=0 --清算类别
     LEFT JOIN WINDBA.SETTLEMENT_INSUR_REIM_ITEM_EXT EXT3 ON  D.SETL_INSUR_REIM_DETAIL_ID=EXT3.SETL_INSUR_REIM_DETAIL_ID
                        AND EXT3.MED_INSUR_REIMBUR_ITEM_CODE=399559994  AND EXT3.IS_DEL=0 --清算经办机构
     LEFT JOIN WINDBA.SETTLEMENT_INSUR_REIM_ITEM REIM ON D.SETL_INSUR_REIM_DETAIL_ID = REIM.SETL_INSUR_REIM_DETAIL_ID
                        AND REIM.IS_DEL=0
	 LEFT JOIN WINDBA.VALUE_SET V3 ON EXT3.SETL_INSUR_REIMBUR_ITEM_MEMO=V3.VALUE_NO AND V3.CODE_SYSTEM_ID=97819 -- 清算机构

    WHERE S.IS_DEL=0 AND S.SETTLEMENT_STATUS = 952093 --完成
                -- AND S.SETTLEMENT_ACCOUNT_STATUS = 307301 -- 正常
          AND S.VALID_FLAG = 98175 and D.MED_INSTI_INTERFACE_ID =1143182103337238528
      AND S.SETTLED_AT BETWEEN (SELECT KSSJ FROM TIME) AND  (SELECT JSSJ FROM TIME) --根据结算时间过滤
--  ETC(AND CHARINDEX(CAST(CHARGING_HOSPITAL_ID AS NVARCHAR(24)), #{CHARGING_HOSPITAL_ID})> 0)

        GROUP BY D.MED_CATEGORY_CODE,EXT2.SETL_INSUR_REIMBUR_ITEM_MEMO,EXT3.SETL_INSUR_REIMBUR_ITEM_MEMO,EXT1.SETL_INSUR_REIMBUR_ITEM_MEMO,
        V1.VALUE_DESC,V2.VALUE_DESC,V3.VALUE_DESC
),
inp_settle_info_dq as (
    SELECT
   '2' as LB,
        EXT3.SETL_INSUR_REIMBUR_ITEM_MEMO 清算机构代码,
        V3.VALUE_DESC 清算机构,
        EXT1.SETL_INSUR_REIMBUR_ITEM_MEMO 险种类型代码,
        V2.VALUE_DESC 险种,
        EXT2.SETL_INSUR_REIMBUR_ITEM_MEMO 清算类别代码,
        EXT2.SETL_INSUR_REIMBUR_ITEM_MEMO 清算类别,
        II.MEDICAL_CATEGORY_CODE 医疗类别代码,
        V1.VALUE_DESC  医疗类别,
        COUNT(DISTINCT S.SETTLEMENT_ID) 笔数,
        SUM(CASE WHEN REIM.MED_INSUR_REIMBUR_ITEM_CODE=399559972 then REIM.SETL_INSUR_REIMBUR_ITEM_AMOUNT ELSE 0 END) 医疗费总金额,
        SUM(CASE WHEN REIM.MED_INSUR_REIMBUR_ITEM_CODE=399559986 then REIM.SETL_INSUR_REIMBUR_ITEM_AMOUNT ELSE 0 END) 基金总金额,
        SUM(CASE WHEN REIM.MED_INSUR_REIMBUR_ITEM_CODE=399559978 then REIM.SETL_INSUR_REIMBUR_ITEM_AMOUNT ELSE 0 END) 统筹基金支付,
        SUM(CASE WHEN REIM.MED_INSUR_REIMBUR_ITEM_CODE=399559988 then REIM.SETL_INSUR_REIMBUR_ITEM_AMOUNT ELSE 0 END) 账户支付,
        SUM(CASE WHEN REIM.MED_INSUR_REIMBUR_ITEM_CODE=399559980 then REIM.SETL_INSUR_REIMBUR_ITEM_AMOUNT ELSE 0 END) 公务员补助,
        SUM(CASE WHEN REIM.MED_INSUR_REIMBUR_ITEM_CODE=399559981 then REIM.SETL_INSUR_REIMBUR_ITEM_AMOUNT ELSE 0 END) 企业补助,
        SUM(CASE WHEN REIM.MED_INSUR_REIMBUR_ITEM_CODE=399559982 then REIM.SETL_INSUR_REIMBUR_ITEM_AMOUNT ELSE 0 END) 居民大病,
        SUM(CASE WHEN REIM.MED_INSUR_REIMBUR_ITEM_CODE=399559983 then REIM.SETL_INSUR_REIMBUR_ITEM_AMOUNT ELSE 0 END) 职工大额,
        SUM(CASE WHEN REIM.MED_INSUR_REIMBUR_ITEM_CODE=399559984 then REIM.SETL_INSUR_REIMBUR_ITEM_AMOUNT ELSE 0 END) 医疗救助,
        SUM(CASE WHEN REIM.MED_INSUR_REIMBUR_ITEM_CODE=399559985 then REIM.SETL_INSUR_REIMBUR_ITEM_AMOUNT ELSE 0 END) 其他支出,
        SUM(CASE WHEN REIM.MED_INSUR_REIMBUR_ITEM_CODE=399559989 then REIM.SETL_INSUR_REIMBUR_ITEM_AMOUNT ELSE 0 END) 个人现金,
        SUM(CASE WHEN REIM.MED_INSUR_REIMBUR_ITEM_CODE=399559990 then REIM.SETL_INSUR_REIMBUR_ITEM_AMOUNT ELSE 0 END) 医院负担金额
        FROM WINDBA.INPATIENT_SETTLEMENT S
     JOIN WINDBA.INP_SETT_INSUR_REIM_DETAIL D ON S.SETTLEMENT_ID=D.SETTLEMENT_ID AND D.IS_DEL=0 AND D.THIRD_PARTY_TXN_SEQ_NO IS NOT NULL --医保
     JOIN WINDBA.INPATIENT_ENCOUNTER IE ON S.ENCOUNTER_ID = IE.ENCOUNTER_ID AND IE.IS_DEL = 0
     LEFT JOIN WINDBA.INPATIENT_INSURANCE II ON S.ENCOUNTER_ID = II.ENCOUNTER_ID AND II.IS_DEL = 0
     LEFT JOIN WINDBA.VALUE_SET V1 ON II.MEDICAL_CATEGORY_CODE = V1.VALUE_ID --医疗类别
     LEFT JOIN WINDBA.INP_SETT_INSUR_REIM_ITEM EXT1 ON  D.SETL_INSUR_REIM_DETAIL_ID=EXT1.SETL_INSUR_REIM_DETAIL_ID
                        AND EXT1.MED_INSUR_REIMBUR_ITEM_CODE=399559966 AND EXT1.IS_DEL=0 --险种
     LEFT JOIN WINDBA.VALUE_SET V2 ON EXT1.SETL_INSUR_REIMBUR_ITEM_MEMO=V2.VALUE_NO AND V2.CODE_SYSTEM_ID=399628923
     LEFT JOIN WINDBA.INP_SETT_INSUR_REIM_ITEM EXT2 ON  D.SETL_INSUR_REIM_DETAIL_ID=EXT2.SETL_INSUR_REIM_DETAIL_ID
                        AND EXT2.MED_INSUR_REIMBUR_ITEM_CODE=399559996  AND EXT2.IS_DEL=0 --清算类别
     LEFT JOIN WINDBA.INP_SETT_INSUR_REIM_ITEM EXT3 ON  D.SETL_INSUR_REIM_DETAIL_ID=EXT3.SETL_INSUR_REIM_DETAIL_ID
                        AND EXT3.MED_INSUR_REIMBUR_ITEM_CODE=399559994  AND EXT3.IS_DEL=0 --清算经办机构
     LEFT JOIN WINDBA.INP_SETT_INSUR_REIM_ITEM REIM ON D.SETL_INSUR_REIM_DETAIL_ID = REIM.SETL_INSUR_REIM_DETAIL_ID
                        AND REIM.IS_DEL=0
    LEFT JOIN WINDBA.VALUE_SET V3 ON EXT3.SETL_INSUR_REIMBUR_ITEM_MEMO=V3.VALUE_NO AND V3.CODE_SYSTEM_ID=97819 -- 清算机构

    WHERE S.IS_DEL=0 AND S.SETTLEMENT_STATUS = 952093 --完成
                -- AND S.SETTLEMENT_ACCOUNT_STATUS = 307301 -- 正常
          AND S.VALID_FLAG = 98175 and D.MED_INSTI_INTERFACE_ID =1143182103337238528
 	 AND S.SETTLED_AT BETWEEN  (SELECT KSSJ FROM TIME) AND  (SELECT JSSJ FROM TIME)--根据结算时间过滤
--  ETC(AND CHARINDEX(CAST(CHARGING_HOSPITAL_ID AS NVARCHAR(24)), #{CHARGING_HOSPITAL_ID})> 0)

        GROUP BY II.MEDICAL_CATEGORY_CODE,EXT2.SETL_INSUR_REIMBUR_ITEM_MEMO,EXT3.SETL_INSUR_REIMBUR_ITEM_MEMO,EXT1.SETL_INSUR_REIMBUR_ITEM_MEMO,
        V1.VALUE_DESC,V2.VALUE_DESC,V3.VALUE_DESC
),
SETTLE_TEMP AS (
    select * from outp_settle_info_bt where  1=1
--     ETC(AND CHARINDEX(#{LB_STR},cast(LB as varchar(24)))>0) ETC(AND CHARINDEX(#{YLLB_STR},cast(医疗类别代码 as varchar(24)))>0)
union all
select * from inp_settle_info_bt where 1=1
--     ETC(AND CHARINDEX(#{LB_STR},cast(LB as varchar(24)))>0) ETC(AND CHARINDEX(#{YLLB_STR},cast(医疗类别代码 as varchar(24)))>0)
union all
select * from outp_settle_info_dq where  1=1
--     ETC(AND CHARINDEX(#{LB_STR},cast(LB as varchar(24)))>0) ETC(AND CHARINDEX(#{YLLB_STR},cast(医疗类别代码 as varchar(24)))>0)
union all
select * from inp_settle_info_dq where  1=1
--     ETC(AND CHARINDEX(#{LB_STR},cast(LB as varchar(24)))>0) ETC(AND CHARINDEX(#{YLLB_STR},cast(医疗类别代码 as varchar(24)))>0)

)

SELECT LB,
       清算机构代码,
       清算机构,
       sum(基金总金额+账户支付)
    FROM SETTLE_TEMP
group by LB,清算机构代码,清算机构


